using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;

using System.Data.SQLite;
using DbType = System.Data.DbType;
namespace MvvmOrm.Models.DatabaseModel
{

    /// <summary>
    /// Fields of this database item
    /// </summary>
    public enum Field
    {
        @Id,
        @OutputDirectory,
        @Namespace,
        @DbSystem,
        @UseSchemaFile,
        @SchemaFile,
        @DbSystemVersion,
        @Server,
        @Database,
        @Username,
        @Password,
        @Port,
        @SqlServerUseIntegratedSecurity,
        @MsAccessSecurityType,
        @MsAccessSecurityDatabase
    }

    [Serializable]
    public partial class Row : RowBase
    {
        #region private variables

        [NonSerialized] internal DbConnection _dbConnection;
        [NonSerialized] internal bool _inMovement = false;

        internal System.Int64 _Id = 0;
        internal System.String _OutputDirectory = String.Empty;
        internal System.String _Namespace = String.Empty;
        internal System.Int32 _DbSystem = 0;
        internal System.Boolean _UseSchemaFile = false;
        internal System.String _SchemaFile = null;
        internal System.String _DbSystemVersion = null;
        internal System.String _Server = null;
        internal System.String _Database = String.Empty;
        internal System.String _Username = null;
        internal System.String _Password = null;
        internal System.Int32? _Port = null;
        internal System.Boolean? _SqlServerUseIntegratedSecurity = null;
        internal System.Int32? _MsAccessSecurityType = null;
        internal System.String _MsAccessSecurityDatabase = null;
        MvvmOrm.Models.Log.Rows _child_Logs = null;
        MvvmOrm.Models.DatabaseItem.Rows _child_DatabaseItems = null;

        #endregion

        #region properties

        public System.Int64 @Id
        {
            get {
                return _Id;
            }
            set {
                if (value != @Id) {
                    _Id = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@Id);
                    RaisePropertyChanged("Id");
                }
            }
        }
        public System.String @OutputDirectory
        {
            get {
                return _OutputDirectory;
            }
            set {
                if (value != @OutputDirectory) {
                    _OutputDirectory = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@OutputDirectory);
                    RaisePropertyChanged("OutputDirectory");
                }
            }
        }
        public System.String @Namespace
        {
            get {
                return _Namespace;
            }
            set {
                if (value != @Namespace) {
                    _Namespace = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@Namespace);
                    RaisePropertyChanged("Namespace");
                }
            }
        }
        public System.Int32 @DbSystem
        {
            get {
                return _DbSystem;
            }
            set {
                if (value != @DbSystem) {
                    _DbSystem = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@DbSystem);
                    RaisePropertyChanged("DbSystem");
                }
            }
        }
        public System.Boolean @UseSchemaFile
        {
            get {
                return _UseSchemaFile;
            }
            set {
                if (value != @UseSchemaFile) {
                    _UseSchemaFile = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@UseSchemaFile);
                    RaisePropertyChanged("UseSchemaFile");
                }
            }
        }
        public System.String @SchemaFile
        {
            get {
                return _SchemaFile;
            }
            set {
                if (value != @SchemaFile) {
                    _SchemaFile = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@SchemaFile);
                    RaisePropertyChanged("SchemaFile");
                }
            }
        }
        public System.String @DbSystemVersion
        {
            get {
                return _DbSystemVersion;
            }
            set {
                if (value != @DbSystemVersion) {
                    _DbSystemVersion = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@DbSystemVersion);
                    RaisePropertyChanged("DbSystemVersion");
                }
            }
        }
        public System.String @Server
        {
            get {
                return _Server;
            }
            set {
                if (value != @Server) {
                    _Server = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@Server);
                    RaisePropertyChanged("Server");
                }
            }
        }
        public System.String @Database
        {
            get {
                return _Database;
            }
            set {
                if (value != @Database) {
                    _Database = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@Database);
                    RaisePropertyChanged("Database");
                }
            }
        }
        public System.String @Username
        {
            get {
                return _Username;
            }
            set {
                if (value != @Username) {
                    _Username = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@Username);
                    RaisePropertyChanged("Username");
                }
            }
        }
        public System.String @Password
        {
            get {
                return _Password;
            }
            set {
                if (value != @Password) {
                    _Password = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@Password);
                    RaisePropertyChanged("Password");
                }
            }
        }
        public System.Int32? @Port
        {
            get {
                return _Port;
            }
            set {
                if (value != @Port) {
                    _Port = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@Port);
                    RaisePropertyChanged("Port");
                }
            }
        }
        public System.Boolean? @SqlServerUseIntegratedSecurity
        {
            get {
                return _SqlServerUseIntegratedSecurity;
            }
            set {
                if (value != @SqlServerUseIntegratedSecurity) {
                    _SqlServerUseIntegratedSecurity = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@SqlServerUseIntegratedSecurity);
                    RaisePropertyChanged("SqlServerUseIntegratedSecurity");
                }
            }
        }
        public System.Int32? @MsAccessSecurityType
        {
            get {
                return _MsAccessSecurityType;
            }
            set {
                if (value != @MsAccessSecurityType) {
                    _MsAccessSecurityType = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@MsAccessSecurityType);
                    RaisePropertyChanged("MsAccessSecurityType");
                }
            }
        }
        public System.String @MsAccessSecurityDatabase
        {
            get {
                return _MsAccessSecurityDatabase;
            }
            set {
                if (value != @MsAccessSecurityDatabase) {
                    _MsAccessSecurityDatabase = value;
                    if (_dbConnection != null)
                        Operation.Update(_dbConnection, this, Field.@MsAccessSecurityDatabase);
                    RaisePropertyChanged("MsAccessSecurityDatabase");
                }
            }
        }


        [NonSerialized] bool _isSelected;
        public bool IsSelected
        {
            get { return _isSelected; }
            set {
                if (value == _isSelected)
                    return;
                _isSelected = value;
                RaisePropertyChanged(() => this.IsSelected);
            }
        }

        [NonSerialized] bool _isExpanded;
        public bool IsExpanded
        {
            get { return _isExpanded; }
            set {
                if (value == _isExpanded)
                    return;
                _isExpanded = value;
                RaisePropertyChanged(() => this.IsExpanded);
            }
        }


        public List<MvvmOrm.Models.Log.Param> Child_Logs_OrderBy { get; set; }
        public MvvmOrm.Models.Log.Rows Child_Logs
        {
            get
            {
                if (_child_Logs == null) {
                    if (_dbConnection == null)
                        _child_Logs = new MvvmOrm.Models.Log.Rows(Child_Logs_OrderBy);
                    else {
                        _child_Logs = MvvmOrm.Models.Log.Operation.Select(_dbConnection, this, Child_Logs_OrderBy, true);
                        foreach (var row in _child_Logs)
                            row._parent_DatabaseModel = this;
                    }
                    _child_Logs.Parent_DatabaseModel = this;
                }
                return _child_Logs;
            }
        }

        public List<MvvmOrm.Models.DatabaseItem.Param> Child_DatabaseItems_OrderBy { get; set; }
        public MvvmOrm.Models.DatabaseItem.Rows Child_DatabaseItems
        {
            get
            {
                if (_child_DatabaseItems == null) {
                    if (_dbConnection == null)
                        _child_DatabaseItems = new MvvmOrm.Models.DatabaseItem.Rows(Child_DatabaseItems_OrderBy);
                    else {
                        _child_DatabaseItems = MvvmOrm.Models.DatabaseItem.Operation.Select(_dbConnection, this, Child_DatabaseItems_OrderBy, true);
                        foreach (var row in _child_DatabaseItems)
                            row._parent_DatabaseModel = this;
                    }
                    _child_DatabaseItems.Parent_DatabaseModel = this;
                }
                return _child_DatabaseItems;
            }
        }

        public IComparable this[Field field]
        {
            get
            {
                switch (field) {
                case Field.@Id:
                    return @Id;
                case Field.@OutputDirectory:
                    return @OutputDirectory;
                case Field.@Namespace:
                    return @Namespace;
                case Field.@DbSystem:
                    return @DbSystem;
                case Field.@UseSchemaFile:
                    return @UseSchemaFile;
                case Field.@SchemaFile:
                    return @SchemaFile;
                case Field.@DbSystemVersion:
                    return @DbSystemVersion;
                case Field.@Server:
                    return @Server;
                case Field.@Database:
                    return @Database;
                case Field.@Username:
                    return @Username;
                case Field.@Password:
                    return @Password;
                case Field.@Port:
                    return @Port;
                case Field.@SqlServerUseIntegratedSecurity:
                    return @SqlServerUseIntegratedSecurity;
                case Field.@MsAccessSecurityType:
                    return @MsAccessSecurityType;
                case Field.@MsAccessSecurityDatabase:
                    return @MsAccessSecurityDatabase;

                }
                throw new InvalidEnumArgumentException(
                            "The field " + field + " doesn't implement IComparable");
            }
        }

        #endregion

        #region constructors / destructors

        partial void PostInitialize();
        partial void PreDispose();
        partial void OnPropertyChanged(string propertyName);

        void Row_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            OnPropertyChanged(e.PropertyName);
        }

        public override void Dispose()
        {
            PreDispose();
            PropertyChanged -= new PropertyChangedEventHandler(Row_PropertyChanged);

            if (_dbConnection != null)
                _dbConnection = null;

            if (_child_Logs != null) _child_Logs = null;
            if (_child_DatabaseItems != null) _child_DatabaseItems = null;

        }

        /// <summary>
        /// Inititalizes a new instance of the Row class
        /// using the specified values for the different fields.
        /// </summary>
        public Row (
            System.Int64 @Id,
            System.String @OutputDirectory,
            System.String @Namespace,
            System.Int32 @DbSystem,
            System.Boolean @UseSchemaFile,
            System.String @SchemaFile,
            System.String @DbSystemVersion,
            System.String @Server,
            System.String @Database,
            System.String @Username,
            System.String @Password,
            System.Int32? @Port,
            System.Boolean? @SqlServerUseIntegratedSecurity,
            System.Int32? @MsAccessSecurityType,
            System.String @MsAccessSecurityDatabase,
DbConnection conn = null) {
            _Id = @Id;
            _OutputDirectory = @OutputDirectory;
            _Namespace = @Namespace;
            _DbSystem = @DbSystem;
            _UseSchemaFile = @UseSchemaFile;
            _SchemaFile = @SchemaFile;
            _DbSystemVersion = @DbSystemVersion;
            _Server = @Server;
            _Database = @Database;
            _Username = @Username;
            _Password = @Password;
            _Port = @Port;
            _SqlServerUseIntegratedSecurity = @SqlServerUseIntegratedSecurity;
            _MsAccessSecurityType = @MsAccessSecurityType;
            _MsAccessSecurityDatabase = @MsAccessSecurityDatabase;

            _dbConnection = conn;
            PropertyChanged += new PropertyChangedEventHandler(Row_PropertyChanged);
            PostInitialize();
       }

        /// <summary>
        /// Inititalizes a new instance of the Row class
        /// </summary>
        public Row(DbConnection conn = null)
        {
            _Id = 0;
            _OutputDirectory = String.Empty;
            _Namespace = String.Empty;
            _DbSystem = 0;
            _UseSchemaFile = false;
            _SchemaFile = null;
            _DbSystemVersion = null;
            _Server = null;
            _Database = String.Empty;
            _Username = null;
            _Password = null;
            _Port = null;
            _SqlServerUseIntegratedSecurity = null;
            _MsAccessSecurityType = null;
            _MsAccessSecurityDatabase = null;

            _dbConnection = conn;
            PropertyChanged += new PropertyChangedEventHandler(Row_PropertyChanged);
            PostInitialize();
        }

        #endregion

        #region public methods

        /// <summary>
        /// Duplicates this instance of Row
        /// </summary>
        /// <returns>The new copy of Row</returns>
        public Row Clone(bool copyDbConnection = false)
        {
            return new Row(
            @Id,
            @OutputDirectory,
            @Namespace,
            @DbSystem,
            @UseSchemaFile,
            @SchemaFile,
            @DbSystemVersion,
            @Server,
            @Database,
            @Username,
            @Password,
            @Port,
            @SqlServerUseIntegratedSecurity,
            @MsAccessSecurityType,
            @MsAccessSecurityDatabase,
            (copyDbConnection ? _dbConnection : null));
        }

        #endregion
    }

    public class Rows : ObservableCollection<Row>, IDisposable
    {
        DbConnection _dbConnection;
        List<Param> _orderBys;

        IEnumerable<Param> OrderBys
        {
            get { return _orderBys; }
            set { _orderBys = (value == null ? null : new List<Param>(value)); }
        }

        public Rows(IEnumerable<Param> orderBys = null)
            : base() 
        {
            OrderBys = orderBys;
        }

        public Rows(IEnumerable<Row> rows, IEnumerable<Param> orderBys = null) 
            : base(rows) 
        { 
            OrderBys = orderBys;

            foreach (Row row in rows)
                row.PropertyChanged += new PropertyChangedEventHandler(row_PropertyChanged);
        }

        internal Rows(DbConnection conn, IEnumerable<Param> orderBys = null)
            : base()
        {
            OrderBys = orderBys;
            _dbConnection = conn;
        }

        internal Rows(DbConnection conn, IEnumerable<Row> rows, IEnumerable<Param> orderBys = null)
            : base(rows)
        {
            OrderBys = orderBys;
            _dbConnection = conn;

            foreach (Row row in rows)
                row.PropertyChanged += new PropertyChangedEventHandler(row_PropertyChanged);
        }

        void row_PropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            Row row = sender as Row;
            if (row == null || OrderBys == null)
                return;
            if (OrderBys.Any(o => o.Field.ToString() == e.PropertyName))
                Move(this.IndexOf(row), GetOrderedIndex(row));
        }

        bool RowIsAfter(Row row, Row baseRow)
        {
            foreach (int i in _orderBys.Select(o => (o.OrderByAsc ? 1 : -1) * row[o.Field].CompareTo(baseRow[o.Field])))
                if (i == 0)
                    continue;
                else 
                    return i > 0;
            return false;
        }

        int GetOrderedIndex(Row row)
        {
            int index = 0;
            foreach (Row row2 in this.Where(r => r != row))
                if (RowIsAfter(row, row2))
                    index++;
                else
                    return index;
            return index;
        }

        protected override void InsertItem(int index, Row row)
        {

            if (_dbConnection != null && !row._inMovement) {
                _dbConnection.Insert(row);
                row._dbConnection = _dbConnection;
            }

            index = _orderBys == null || _orderBys.Count == 0 ? index : GetOrderedIndex(row);

            row.PropertyChanged += new PropertyChangedEventHandler(row_PropertyChanged);
            base.InsertItem(index, row);
        }

        protected override void RemoveItem(int index)
        {
            Row row = this[index];
            if (!row._inMovement) {

                if (row._dbConnection != null)
                    _dbConnection.Delete(row);

                row.Dispose();
            }
            row.PropertyChanged -= new PropertyChangedEventHandler(row_PropertyChanged);
            base.RemoveItem(index);
        }

        protected override void ClearItems()
        {
            foreach (Row row in this) {

                if (row._dbConnection != null)
                    _dbConnection.Delete(row);

                row.PropertyChanged -= new PropertyChangedEventHandler(row_PropertyChanged);
                row.Dispose();
            }
            base.ClearItems();
        }
        
        public void Dispose()
        {

            _dbConnection = null;
        }
    }


    /// <summary>
    /// Represents a parameter to use with methods of the class Operation.
    /// A Param object represents one Field of the database item.
    /// </summary>
    [Serializable()]
    public class Param
    {
        #region private variables

        string _fieldNameExact;
        DbOperator _dbOperator;
        Field _field;
        bool _orderByAsc;
        bool _isDbNullable;
        bool _isDbNull;
        DbType _dbType;
        object _value;

        #endregion

        #region properties

        /// <summary>
        /// Gets the Field that this instance of Param represents database item.
        /// </summary>
        public Field Field
        {
            get { return _field; }
        }

        /// <summary>
        /// Gets the exact name of the field that this Param instance represents
        /// </summary>
        public string FieldNameExact
        {
            get { return _fieldNameExact; }
        }

        /// <summary>
        /// Gets the exact name of the field formatted for SQL queries
        /// </summary>
        public string ColumnNameEscaped
        {
            get { return Operation.EscapeStart + _fieldNameExact + Operation.EscapeEnd; }
        }

        /// <summary>
        /// Gets or sets the value of this instance of Param
        /// </summary>
        /// <exception cref="System.Exception">Thrown when the value provided cannot be 
        /// converted to the type of the field.</exception>
        /// <exception cref="System.Exception">Thrown when the text or string value's 
        /// lentgth is bigger than the maximum for this field in the database item.</exception>
        public object Value
        {
            get { return _value; }
            set
            {
                switch (_field)
                {

                    case Field.@Id:
                        _value = Convert.ChangeType(value, typeof(System.Int64));
                        break;
                    case Field.@OutputDirectory:
                        _value = Convert.ChangeType(value, typeof(System.String));
                        break;
                    case Field.@Namespace:
                        _value = Convert.ChangeType(value, typeof(System.String));
                        break;
                    case Field.@DbSystem:
                        _value = Convert.ChangeType(value, typeof(System.Int32));
                        break;
                    case Field.@UseSchemaFile:
                        _value = Convert.ChangeType(value, typeof(System.Boolean));
                        break;
                    case Field.@SchemaFile:
                        _value = Convert.ChangeType(value, typeof(System.String));
                        break;
                    case Field.@DbSystemVersion:
                        _value = Convert.ChangeType(value, typeof(System.String));
                        break;
                    case Field.@Server:
                        _value = Convert.ChangeType(value, typeof(System.String));
                        break;
                    case Field.@Database:
                        _value = Convert.ChangeType(value, typeof(System.String));
                        break;
                    case Field.@Username:
                        _value = Convert.ChangeType(value, typeof(System.String));
                        break;
                    case Field.@Password:
                        _value = Convert.ChangeType(value, typeof(System.String));
                        break;
                    case Field.@Port:
                        _value = Convert.ChangeType(value, typeof(System.Int32));
                        break;
                    case Field.@SqlServerUseIntegratedSecurity:
                        _value = Convert.ChangeType(value, typeof(System.Boolean));
                        break;
                    case Field.@MsAccessSecurityType:
                        _value = Convert.ChangeType(value, typeof(System.Int32));
                        break;
                    case Field.@MsAccessSecurityDatabase:
                        _value = Convert.ChangeType(value, typeof(System.String));
                        break;

                }
                _isDbNull = false;
            }
        }

        /// <summary>
        /// Gets the DbType of the field represented by this instance of Param.
        /// </summary>
        public DbType DbType
        {
            get { return _dbType; }
        }

        /// <summary>
        /// Gets or sets the DbOperator that will be used for mean of
        /// comparison when this instance of Param is used in a Where clause.
        /// </summary>
        public DbOperator DbOperator
        {
            get { return _dbOperator; }
            set { _dbOperator = value; }
        }

        /// <summary>
        /// Gets or sets the the sort direction for when this Param instance is used 
        /// in an Order By clause.
        /// </summary>
        public bool OrderByAsc
        {
            get { return _orderByAsc; }
            set { _orderByAsc = value; }
        }

        /// <summary>
        /// Returns true if the field held by this instance of Param
        /// can hold a database null value, otherwise false.
        /// </summary>
        public bool IsDbNullable
        {
            get { return _isDbNullable; }
        }

        /// <summary>
        /// Get or set the value of this parameter at database null.
        /// </summary>
        /// <exception cref="System.Exception">Thrown when the field held by this 
        /// instance of Param can't hold a database null value.</exception>
        public bool IsDbNull
        {
            get { return _isDbNull; }
            set {
                if (!_isDbNullable && value)
                    throw new Exception("Field "
                        + _fieldNameExact.ToString()
                        + " can't be null");
                _isDbNull = value;
            }
        }

        #endregion

        #region constructors / destructors

        /// <summary>
        /// Inititalizes a new instance of the Param class using the specified Field name.
        /// </summary>
        /// <param name="field">The Field that this instance of Param will represent</param>
        public Param(Field field) 
            : this(field, DbOperator.EQUAL, null)
        {
        }

        /// <summary>
        /// Inititalizes a new instance of the Param class using the 
        /// specified Field name and the sort direction used in an Order By clause.
        /// </summary>
        /// <param name="field">The Field that this instance of Param will represent</param>
        /// <param name="orderByAsc">The sort direction for when this instance of Param 
        /// is used in an Order By clause</param>
        public Param(Field field, bool orderByAsc)
        {
            InitParam(field);
            _orderByAsc = orderByAsc;
        }

        /// <summary>
        /// Inititalizes a new instance of the Param class
        /// using the specified Field name and the comparison operator for when used in a Where clause.
        /// </summary>
        /// <param name="field">The Field that this instance of Param will represent</param>
        /// <param name="dbOperator">The comparison operator for when this instance of Param 
        /// is used in a Where clause</param>
        public Param(Field field, DbOperator dbOperator) 
            : this(field, dbOperator, null)
        {
        }

        /// <summary>
        /// Inititalizes a new instance of the Param class 
        /// using the specified Field name, the comparison operator for when
        /// used in a Where clause and the value it initially holds.
        /// </summary>
        /// <param name="field">The Field that this instance of Param will represent</param>
        /// <param name="dbOperator">The comparison operator for when this instance of Param 
        /// is used in a Where clause</param>
        /// <param name="value">The value that this instance of Param initially holds</param>
        public Param(Field field, DbOperator dbOperator, object value)
        {
            InitParam(field);
            if (value != null)
                Value = value;
            _dbOperator = dbOperator;
            _orderByAsc = true;
        }

        #endregion

        #region private functions

        void InitParam(Field field)
        {
            switch (field)
            {

                case Field.@Id:
                    _fieldNameExact = "Id";
                    _dbType = DbType.Int64;
                    _value = (System.Int64)0;
                    _isDbNullable = false;
                    break;
                case Field.@OutputDirectory:
                    _fieldNameExact = "OutputDirectory";
                    _dbType = DbType.String;
                    _value = (System.String)String.Empty;
                    _isDbNullable = false;
                    break;
                case Field.@Namespace:
                    _fieldNameExact = "Namespace";
                    _dbType = DbType.String;
                    _value = (System.String)String.Empty;
                    _isDbNullable = false;
                    break;
                case Field.@DbSystem:
                    _fieldNameExact = "DbSystem";
                    _dbType = DbType.Int32;
                    _value = (System.Int32)0;
                    _isDbNullable = false;
                    break;
                case Field.@UseSchemaFile:
                    _fieldNameExact = "UseSchemaFile";
                    _dbType = DbType.Boolean;
                    _value = (System.Boolean)false;
                    _isDbNullable = false;
                    break;
                case Field.@SchemaFile:
                    _fieldNameExact = "SchemaFile";
                    _dbType = DbType.String;
                    _value = (System.String)null;
                    _isDbNullable = true;
                    break;
                case Field.@DbSystemVersion:
                    _fieldNameExact = "DbSystemVersion";
                    _dbType = DbType.String;
                    _value = (System.String)null;
                    _isDbNullable = true;
                    break;
                case Field.@Server:
                    _fieldNameExact = "Server";
                    _dbType = DbType.String;
                    _value = (System.String)null;
                    _isDbNullable = true;
                    break;
                case Field.@Database:
                    _fieldNameExact = "Database";
                    _dbType = DbType.String;
                    _value = (System.String)String.Empty;
                    _isDbNullable = false;
                    break;
                case Field.@Username:
                    _fieldNameExact = "Username";
                    _dbType = DbType.String;
                    _value = (System.String)null;
                    _isDbNullable = true;
                    break;
                case Field.@Password:
                    _fieldNameExact = "Password";
                    _dbType = DbType.String;
                    _value = (System.String)null;
                    _isDbNullable = true;
                    break;
                case Field.@Port:
                    _fieldNameExact = "Port";
                    _dbType = DbType.Int32;
                    _value = (System.Int32?)null;
                    _isDbNullable = true;
                    break;
                case Field.@SqlServerUseIntegratedSecurity:
                    _fieldNameExact = "SqlServerUseIntegratedSecurity";
                    _dbType = DbType.Boolean;
                    _value = (System.Boolean?)null;
                    _isDbNullable = true;
                    break;
                case Field.@MsAccessSecurityType:
                    _fieldNameExact = "MsAccessSecurityType";
                    _dbType = DbType.Int32;
                    _value = (System.Int32?)null;
                    _isDbNullable = true;
                    break;
                case Field.@MsAccessSecurityDatabase:
                    _fieldNameExact = "MsAccessSecurityDatabase";
                    _dbType = DbType.String;
                    _value = (System.String)null;
                    _isDbNullable = true;
                    break;

            }
            _isDbNull = false;
            _field = field;
        }

        #endregion
    }


    /// <summary>
    /// Represents the layer that interfaces between the data and the database.
    /// Manages the connection, transactions and data operations for the database
    /// item it represents.
    /// </summary>
    public static class Operation
    {
        public const string ItemName = "DatabaseModel";

        public const string EscapeStart = "\"";
        public const string EscapeEnd = "\"";

        #region public functions

        /// <summary>Clones the rows</summary>
        /// <returns>The new copy of Rows</returns>
        public static IEnumerable<Row> Clone(IEnumerable<Row> rows)
        {
            foreach (Row row in rows)
                yield return row.Clone();
        }

        /// <summary>Get Max of a field of this DB item</summary>
        /// <param name="field">The Field for which to find the maximum value</param>
        /// <returns>The maximum value found or DbNull</returns>
        public static object Max(this DbConnection conn, Field field)
        {
            List<Param> @params = new List<Param>();
            return Max(conn, field, @params);
        }

        /// <summary>Get Max of a field of this DB itemusing a Param for the Where clause</summary>
        /// <param name="field">The Field for which to find the maximum value</param>
        /// <param name="param">The Param object used for the Where clause</param>
        /// <returns>The maximum value found or DbNull</returns>
        public static object Max(this DbConnection conn, Field field, Param param)
        {
            List<Param> @params = new List<Param>();
            @params.Add(param);
            return Max(conn, field, @params);
        }

        /// <summary>
        /// Get the maximum value for a specified field
        /// of this database item using a collection of Param for the Where clause
        /// </summary>
        /// <param name="field">The Field for which to find the maximum value</param>
        /// <param name="@params">The Param collection object used for the 
        /// Where clause</param>
        /// <returns>The maximum value found or DbNull</returns>
        public static object Max(this DbConnection conn, Field field, IEnumerable<Param> @params)
        {
            using (var command = new SQLiteCommand()) {
                Param param = new Param(field);
                command.CommandText = "SELECT MAX(" + param.ColumnNameEscaped + ") FROM \"DatabaseModel\" ";
                BuildWhereClause(command, @params);
                return conn.ExecuteScalar(command);
            }
        }

        /// <summary>Get the miminum value for a specified field of this database item </summary>
        /// <param name="field">The Field for which to find the minimum value</param>
        /// <returns>The minimum value found or DbNull</returns>
        public static object Min(this DbConnection conn, Field field)
        {
            List<Param> @params = new List<Param>();
            return Min(conn, field, @params);
        }

        /// <summary>
        /// Get the miminum value for a specified field
        /// of this database item using a Param for the Where clause
        /// </summary>
        /// <param name="field">The Field for which to find the miminum value</param>
        /// <param name="param">The Param object used for the
        /// Where clause</param>
        /// <returns>The miminum value found or DbNull</returns>
        public static object Min(this DbConnection conn, Field field, Param ParamForWhereClause)
        {
            List<Param> @params = new List<Param>();
            @params.Add(ParamForWhereClause);
            return Min(conn, field, @params);
        }

        /// <summary>
        /// Get the miminum value for a specified field
        /// of this database item using a collection of Param for the Where clause
        /// </summary>
        /// <param name="field">The Field for which to find the miminum value</param>
        /// <param name="@params">The Param collection object used for the
        /// Where clause</param>
        /// <returns>The miminum value found or DbNull</returns>
        public static object Min(this DbConnection conn, Field field, IEnumerable<Param> @params)
        {
            using (var command = new SQLiteCommand()) {
                Param param = new Param(field);
                command.CommandText = "SELECT MIN(" + param.ColumnNameEscaped + ") FROM \"DatabaseModel\" ";
                BuildWhereClause(command, @params);
                return conn.ExecuteScalar(command);
            }
        }

        /// <summary>Gets the row count in this DB item</summary>
        /// <returns>The row count</returns>
        public static long Count(this DbConnection conn)
        {
            return Count(conn, new List<Param>());
        }

        /// <summary>Gets the row count in this DB item</summary>
        /// <param name="param">The Param object used for the Where clause</param>
        /// <returns>The row count</returns>
        public static long Count(this DbConnection conn, Param param)
        {
            List<Param> @params = new List<Param>();
            @params.Add(param);
            return Count(conn, @params);
        }

        /// <summary>Gets the row count in this DB item</summary>
        /// <param name="@params">The Param collection object used for the Where clause</param>
        /// <returns>The row count</returns>
        public static long Count(this DbConnection conn, IEnumerable<Param> @params)
        {
            using (var command = new SQLiteCommand()) {
                command.CommandText = "SELECT COUNT(*) FROM \"DatabaseModel\" ";
                BuildWhereClause(command, @params);
                return (long)conn.ExecuteScalar(command);
            }
        }

        /// <summary>Select the row with the specified primary key</summary>
        /// <param name="PK_Id">Primary key value</param>
        /// <returns>The row for the primary key if it exists, null otherwise</returns>
        public static Row Select(this DbConnection conn, System.Int64 PK_Id)
        {
            List<Param> @params = new List<Param>(1);
            @params.Add(new Param(Field.@Id, DbOperator.EQUAL, PK_Id));
            Rows rows = Select(conn, @params);
            return rows.Count == 1 ? rows[0] : null;
        }


        /// <summary>
        /// Select all rows.
        /// </summary>
        /// <param name="conn"></param>
        /// <returns>Selected rows</returns>
        public static Rows Select(this DbConnection conn, bool linkWithDb = false)
        {
            return Select(conn, paramsWhere: null, paramsOrderBy: null, linkWithDb: linkWithDb);
        }

        /// <summary>Select rows</summary>
        /// <param name="paramWhere">The Param for the Where clause</param>
        /// <param name="paramOrderBy">The Param for the Order By clause</param>
        /// <returns>The selected rows</returns>
        public static Rows Select(this DbConnection conn, Param paramWhere, Param paramOrderBy, bool linkWithDb = false)
        {
            return Select(conn, 
                paramWhere == null ? null : new[] { paramWhere },
                paramOrderBy == null ? null : new[] { paramOrderBy },
                linkWithDb);
        }

        /// <summary>Select rows</summary>
        /// <param name="paramsWhere">The Param list for the Where clause</param>
        /// <param name="paramOrderBy">The Param for the Order By clause</param>
        /// <returns>The selected rows</returns>
        public static Rows Select(this DbConnection conn, IEnumerable<Param> paramsWhere, Param paramOrderBy, bool linkWithDb = false)
        {
            return Select(conn, 
                paramsWhere, 
                paramOrderBy == null ? null : new[] { paramOrderBy },
                linkWithDb);
        }

        /// <summary>Select rows</summary>
        /// <param name="paramWhere">The Param for the Where clause</param>
        /// <param name="paramsOrderBy">The Param list for the Order By clause</param>
        /// <returns>The selected rows</returns>
        public static Rows Select(this DbConnection conn, Param paramWhere, IEnumerable<Param> paramsOrderBy, bool linkWithDb = false)
        {
            return Select(conn, 
                paramWhere == null ? null : new[] { paramWhere },
                paramsOrderBy,
                linkWithDb);
        }

        /// <summary>Select rows</summary>
        /// <param name="paramsWhere">The Param list for the Where clause</param>
        /// <param name="paramsOrderBy">The Param list for the Order By clause</param>
        /// <param name="linkWithDb">Insert/Delete in database when adding/removing rows.</param>
        /// <returns>The selected rows</returns>
        public static Rows Select(this DbConnection conn, IEnumerable<Param> paramsWhere, IEnumerable<Param> paramsOrderBy = null, bool linkWithDb = false)
        {
            using (var command = new SQLiteCommand("SELECT \"Id\", \"OutputDirectory\", \"Namespace\", \"DbSystem\", \"UseSchemaFile\", \"SchemaFile\", \"DbSystemVersion\", \"Server\", \"Database\", \"Username\", \"Password\", \"Port\", \"SqlServerUseIntegratedSecurity\", \"MsAccessSecurityType\", \"MsAccessSecurityDatabase\" FROM \"DatabaseModel\" ")) {

                BuildWhereClause(command, paramsWhere);
                BuildOrderByClause(command, paramsOrderBy);
                if (linkWithDb)
                    return new Rows(conn, conn.Select(command), paramsOrderBy);
                else
                    return new Rows(conn.Select(command), paramsOrderBy);
            }
        }

        internal static IEnumerable<Row> Select(this DbConnection conn, SQLiteCommand command)
        {
            conn.ManageAutoConnect();
            using (var dr = conn.ExecuteReader(command)) {
                while (dr.Read()) {
                    Row row = new Row(
                    dr.GetInt64(0),
                    dr.GetString(1),
                    dr.GetString(2),
                    dr.GetInt32(3),
                    dr.GetBoolean(4),
                    dr.IsDBNull(5) ? (System.String)null : dr.GetString(5),
                    dr.IsDBNull(6) ? (System.String)null : dr.GetString(6),
                    dr.IsDBNull(7) ? (System.String)null : dr.GetString(7),
                    dr.GetString(8),
                    dr.IsDBNull(9) ? (System.String)null : dr.GetString(9),
                    dr.IsDBNull(10) ? (System.String)null : dr.GetString(10),
                    dr.IsDBNull(11) ? (System.Int32?)null : dr.GetInt32(11),
                    dr.IsDBNull(12) ? (System.Boolean?)null : dr.GetBoolean(12),
                    dr.IsDBNull(13) ? (System.Int32?)null : dr.GetInt32(13),
                    dr.IsDBNull(14) ? (System.String)null : dr.GetString(14),
                    conn);

                    yield return row;
                }
                dr.Close();
            }
            conn.ManageAutoDisconnect();
        }

        /// <summary>Inserts a Row</summary>
        /// <param name="row">Row to insert</param>
        public static void Insert(this DbConnection conn, Row row)
        {
            Insert(conn, new[] { row });
        }

        /// <summary>Inserts a list of Rows</summary>
        /// <param name="rows">Row list to insert.</param>
        public static void Insert(this DbConnection conn, IEnumerable<Row> rows)
        {
            using (var command = new SQLiteCommand()) {
                command.CommandText = "INSERT INTO \"DatabaseModel\" (\"OutputDirectory\", \"Namespace\", \"DbSystem\", \"UseSchemaFile\", \"SchemaFile\", \"DbSystemVersion\", \"Server\", \"Database\", \"Username\", \"Password\", \"Port\", \"SqlServerUseIntegratedSecurity\", \"MsAccessSecurityType\", \"MsAccessSecurityDatabase\") VALUES (@OutputDirectory, @Namespace, @DbSystem, @UseSchemaFile, @SchemaFile, @DbSystemVersion, @Server, @Database, @Username, @Password, @Port, @SqlServerUseIntegratedSecurity, @MsAccessSecurityType, @MsAccessSecurityDatabase);";
                command.CommandText += "SELECT last_insert_rowid();";
                command.Parameters.Add(new SQLiteParameter("@OutputDirectory", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Namespace", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@DbSystem", DbType.Int32));
                command.Parameters.Add(new SQLiteParameter("@UseSchemaFile", DbType.Boolean));
                command.Parameters.Add(new SQLiteParameter("@SchemaFile", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@DbSystemVersion", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Server", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Database", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Username", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Password", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Port", DbType.Int32));
                command.Parameters.Add(new SQLiteParameter("@SqlServerUseIntegratedSecurity", DbType.Boolean));
                command.Parameters.Add(new SQLiteParameter("@MsAccessSecurityType", DbType.Int32));
                command.Parameters.Add(new SQLiteParameter("@MsAccessSecurityDatabase", DbType.String));
                foreach (Row row in rows) {
                    row._dbConnection = conn;
                    command.Parameters["@OutputDirectory"].Value = row._OutputDirectory;
                    command.Parameters["@Namespace"].Value = row._Namespace;
                    command.Parameters["@DbSystem"].Value = row._DbSystem;
                    command.Parameters["@UseSchemaFile"].Value = row._UseSchemaFile;
                    command.Parameters["@SchemaFile"].Value = row._SchemaFile ?? (object)DBNull.Value;
                    command.Parameters["@DbSystemVersion"].Value = row._DbSystemVersion ?? (object)DBNull.Value;
                    command.Parameters["@Server"].Value = row._Server ?? (object)DBNull.Value;
                    command.Parameters["@Database"].Value = row._Database;
                    command.Parameters["@Username"].Value = row._Username ?? (object)DBNull.Value;
                    command.Parameters["@Password"].Value = row._Password ?? (object)DBNull.Value;
                    command.Parameters["@Port"].Value = row._Port ?? (object)DBNull.Value;
                    command.Parameters["@SqlServerUseIntegratedSecurity"].Value = row._SqlServerUseIntegratedSecurity ?? (object)DBNull.Value;
                    command.Parameters["@MsAccessSecurityType"].Value = row._MsAccessSecurityType ?? (object)DBNull.Value;
                    command.Parameters["@MsAccessSecurityDatabase"].Value = row._MsAccessSecurityDatabase ?? (object)DBNull.Value;
                    row._Id = (System.Int64)conn.ExecuteScalar(command);

                }
            }
        }

        /// <summary>Inserts a row</summary>
        /// <param name="@params">Values to insert in the new row</param>
        public static void Insert(this DbConnection conn, IEnumerable<Param> @params)
        {
            Row row = new Row();
            foreach (Param param in @params)
                switch (param.Field) {
                    case Field.@Id:
                        row._Id = (System.Int64)param.Value;
                        break;
                    case Field.@OutputDirectory:
                        row._OutputDirectory = (System.String)param.Value;
                        break;
                    case Field.@Namespace:
                        row._Namespace = (System.String)param.Value;
                        break;
                    case Field.@DbSystem:
                        row._DbSystem = (System.Int32)param.Value;
                        break;
                    case Field.@UseSchemaFile:
                        row._UseSchemaFile = (System.Boolean)param.Value;
                        break;
                    case Field.@SchemaFile:
                        row._SchemaFile = (System.String)param.Value;
                        break;
                    case Field.@DbSystemVersion:
                        row._DbSystemVersion = (System.String)param.Value;
                        break;
                    case Field.@Server:
                        row._Server = (System.String)param.Value;
                        break;
                    case Field.@Database:
                        row._Database = (System.String)param.Value;
                        break;
                    case Field.@Username:
                        row._Username = (System.String)param.Value;
                        break;
                    case Field.@Password:
                        row._Password = (System.String)param.Value;
                        break;
                    case Field.@Port:
                        row._Port = (System.Int32)param.Value;
                        break;
                    case Field.@SqlServerUseIntegratedSecurity:
                        row._SqlServerUseIntegratedSecurity = (System.Boolean)param.Value;
                        break;
                    case Field.@MsAccessSecurityType:
                        row._MsAccessSecurityType = (System.Int32)param.Value;
                        break;
                    case Field.@MsAccessSecurityDatabase:
                        row._MsAccessSecurityDatabase = (System.String)param.Value;
                        break;

                }
            Insert(conn, row);
        }

        /// <summary>Updates a Row</summary>
        /// <param name="row">The Row to update</param>
        /// <returns>1 if a row was updated, 0 otherwise</returns>
        public static int Update(this DbConnection conn, Row row)
        {
            return Update(conn, new[] { row });
        }

        public static int Update(this DbConnection conn, IEnumerable<Row> rows)
        {
            int ret = 0;
            using (var command = new SQLiteCommand()) {
                command.CommandText = "UPDATE \"DatabaseModel\" SET \"OutputDirectory\"=@OutputDirectory, \"Namespace\"=@Namespace, \"DbSystem\"=@DbSystem, \"UseSchemaFile\"=@UseSchemaFile, \"SchemaFile\"=@SchemaFile, \"DbSystemVersion\"=@DbSystemVersion, \"Server\"=@Server, \"Database\"=@Database, \"Username\"=@Username, \"Password\"=@Password, \"Port\"=@Port, \"SqlServerUseIntegratedSecurity\"=@SqlServerUseIntegratedSecurity, \"MsAccessSecurityType\"=@MsAccessSecurityType, \"MsAccessSecurityDatabase\"=@MsAccessSecurityDatabase WHERE \"Id\"=@Id;";
                command.Parameters.Add(new SQLiteParameter("@OutputDirectory", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Namespace", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@DbSystem", DbType.Int32));
                command.Parameters.Add(new SQLiteParameter("@UseSchemaFile", DbType.Boolean));
                command.Parameters.Add(new SQLiteParameter("@SchemaFile", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@DbSystemVersion", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Server", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Database", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Username", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Password", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Port", DbType.Int32));
                command.Parameters.Add(new SQLiteParameter("@SqlServerUseIntegratedSecurity", DbType.Boolean));
                command.Parameters.Add(new SQLiteParameter("@MsAccessSecurityType", DbType.Int32));
                command.Parameters.Add(new SQLiteParameter("@MsAccessSecurityDatabase", DbType.String));
                command.Parameters.Add(new SQLiteParameter("@Id", DbType.Int64));
                foreach (Row row in rows) {
                    command.Parameters["@OutputDirectory"].Value = row._OutputDirectory;
                    command.Parameters["@Namespace"].Value = row._Namespace;
                    command.Parameters["@DbSystem"].Value = row._DbSystem;
                    command.Parameters["@UseSchemaFile"].Value = row._UseSchemaFile;
                    command.Parameters["@SchemaFile"].Value = row._SchemaFile ?? (object)DBNull.Value;
                    command.Parameters["@DbSystemVersion"].Value = row._DbSystemVersion ?? (object)DBNull.Value;
                    command.Parameters["@Server"].Value = row._Server ?? (object)DBNull.Value;
                    command.Parameters["@Database"].Value = row._Database;
                    command.Parameters["@Username"].Value = row._Username ?? (object)DBNull.Value;
                    command.Parameters["@Password"].Value = row._Password ?? (object)DBNull.Value;
                    command.Parameters["@Port"].Value = row._Port ?? (object)DBNull.Value;
                    command.Parameters["@SqlServerUseIntegratedSecurity"].Value = row._SqlServerUseIntegratedSecurity ?? (object)DBNull.Value;
                    command.Parameters["@MsAccessSecurityType"].Value = row._MsAccessSecurityType ?? (object)DBNull.Value;
                    command.Parameters["@MsAccessSecurityDatabase"].Value = row._MsAccessSecurityDatabase ?? (object)DBNull.Value;
                    command.Parameters["@Id"].Value = row._Id;
                    ret += conn.ExecuteNonQuery(command);

                }
                return ret;
            }
        }

        internal static int Update(this DbConnection conn, Row row, Field field)
        {
            const string sql = "UPDATE \"DatabaseModel\" SET \"{0}\"=@S0 WHERE \"Id\"=@Id;";
            using (var command = new SQLiteCommand()) {
                string column = String.Empty;
                SQLiteParameter parameter = null;
                switch (field) {
                case Field.@OutputDirectory:
                    parameter = new SQLiteParameter("@S0", DbType.String);
                    parameter.Value = row._OutputDirectory;
                    column = "OutputDirectory";
                    break;
                case Field.@Namespace:
                    parameter = new SQLiteParameter("@S0", DbType.String);
                    parameter.Value = row._Namespace;
                    column = "Namespace";
                    break;
                case Field.@DbSystem:
                    parameter = new SQLiteParameter("@S0", DbType.Int32);
                    parameter.Value = row._DbSystem;
                    column = "DbSystem";
                    break;
                case Field.@UseSchemaFile:
                    parameter = new SQLiteParameter("@S0", DbType.Boolean);
                    parameter.Value = row._UseSchemaFile;
                    column = "UseSchemaFile";
                    break;
                case Field.@SchemaFile:
                    parameter = new SQLiteParameter("@S0", DbType.String);
                    if (row._SchemaFile == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = row._SchemaFile;
                    column = "SchemaFile";
                    break;
                case Field.@DbSystemVersion:
                    parameter = new SQLiteParameter("@S0", DbType.String);
                    if (row._DbSystemVersion == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = row._DbSystemVersion;
                    column = "DbSystemVersion";
                    break;
                case Field.@Server:
                    parameter = new SQLiteParameter("@S0", DbType.String);
                    if (row._Server == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = row._Server;
                    column = "Server";
                    break;
                case Field.@Database:
                    parameter = new SQLiteParameter("@S0", DbType.String);
                    parameter.Value = row._Database;
                    column = "Database";
                    break;
                case Field.@Username:
                    parameter = new SQLiteParameter("@S0", DbType.String);
                    if (row._Username == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = row._Username;
                    column = "Username";
                    break;
                case Field.@Password:
                    parameter = new SQLiteParameter("@S0", DbType.String);
                    if (row._Password == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = row._Password;
                    column = "Password";
                    break;
                case Field.@Port:
                    parameter = new SQLiteParameter("@S0", DbType.Int32);
                    if (row._Port == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = row._Port;
                    column = "Port";
                    break;
                case Field.@SqlServerUseIntegratedSecurity:
                    parameter = new SQLiteParameter("@S0", DbType.Boolean);
                    if (row._SqlServerUseIntegratedSecurity == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = row._SqlServerUseIntegratedSecurity;
                    column = "SqlServerUseIntegratedSecurity";
                    break;
                case Field.@MsAccessSecurityType:
                    parameter = new SQLiteParameter("@S0", DbType.Int32);
                    if (row._MsAccessSecurityType == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = row._MsAccessSecurityType;
                    column = "MsAccessSecurityType";
                    break;
                case Field.@MsAccessSecurityDatabase:
                    parameter = new SQLiteParameter("@S0", DbType.String);
                    if (row._MsAccessSecurityDatabase == null)
                        parameter.Value = DBNull.Value;
                    else
                        parameter.Value = row._MsAccessSecurityDatabase;
                    column = "MsAccessSecurityDatabase";
                    break;
                }
                command.Parameters.Add(parameter);
                command.CommandText = String.Format(sql, column);

                parameter = new SQLiteParameter("@Id", DbType.Int64);
                    parameter.Value = row._Id;
                command.Parameters.Add(parameter);


                return conn.ExecuteNonQuery(command);
            }
        }


        /// <summary>
        /// Updates all rows of the database item by setting a field specified by the Param provided.
        /// </summary>
        /// <param name="paramSet">The Param used to update the corresponding
        /// field in the database item.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, Param paramSet)
        {
            return Update(conn, paramSet, new List<Param>());
        }

        /// <summary>
        /// Updates all rows of the database item by setting the fields specified by
        /// the Param collection provided. 
        /// </summary>
        /// <param name="paramsSet">The collection of Param used to update the corresponding
        /// fields in the database item.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, IEnumerable<Param> paramsSet)
        {
            return Update(conn, paramsSet, new List<Param>());
        }

        /// <summary>
        /// Updates the rows in the database item using Param objects
        /// to update fields according to certain criteria.
        /// </summary>
        /// <param name="paramSet">The Param object that specifies the field to update.</param>
        /// <param name="paramWhere">The Param object that specifies the field
        /// to search to find the rows to update.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, Param paramSet, Param paramWhere)
        {
            List<Param> paramsSet = new List<Param>();
            List<Param> paramsWhere = new List<Param>();
            paramsSet.Add(paramSet);
            paramsWhere.Add(paramWhere);
            return Update(conn, paramsSet, paramsWhere);
        }

        /// <summary>
        /// Updates the rows in the database item using Param objects
        /// to update fields according to certain criteria.
        /// </summary>
        /// <param name="paramSet">The Param object that specifies the field
        /// to update.</param>
        /// <param name="paramsWhere">The Param collection object that specifies the fields
        /// to search to find the rows to update.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, Param paramSet, IEnumerable<Param> paramsWhere)
        {
            List<Param> paramsSet = new List<Param>();
            paramsSet.Add(paramSet);
            return Update(conn, paramsSet, paramsWhere);
        }

        /// <summary>
        /// Updates the rows in the database item using Param objects 
        /// to update fields according to certain criteria.
        /// </summary>
        /// <param name="paramsSet">The Param collection object that specifies the fields
        /// to update.</param>
        /// <param name="paramWhere">The Param object that specifies the field
        /// to search to find the rows to update.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(this DbConnection conn, IEnumerable<Param> paramsSet, Param paramWhere)
        {
            List<Param> paramsWhere = new List<Param>();
            paramsWhere.Add(paramWhere);
            return Update(conn, paramsSet, paramsWhere);
        }

        /// <summary>
        /// Updates the rows in the database item using Param objects 
        /// to update fields according to certain criteria.
        /// </summary>
        /// <param name="paramsSet">The Param collection object that specifies the fields
        /// to update.</param>
        /// <param name="paramsWhere">The Param collection object that specifies the fields
        /// to search to find the rows to update.</param>
        /// <returns>The number of updated rows.</returns>
        public static int Update(DbConnection conn, IEnumerable<Param> paramsSet, IEnumerable<Param> paramsWhere)
        {
            using (var command = new SQLiteCommand()) {
                SQLiteParameter parameter;
                int i = 0;
                command.CommandText = "UPDATE \"DatabaseModel\" SET " + string.Join(", ",
                   (from p in paramsSet select p).Select(p => {
                       parameter = new SQLiteParameter("@" + i++, p.DbType);
                       parameter.Value = p.Value ?? (object)DBNull.Value;
                       command.Parameters.Add(parameter);
                       return p.ColumnNameEscaped + "=@" + i;
                   }));
                BuildWhereClause(command, paramsWhere);
                return conn.ExecuteNonQuery(command);
            }
        }

        /// <summary>Deletes all rows</summary>
        /// <returns>The number of deleted rows.</returns>
        public static int Delete(this DbConnection conn)
        {
            return Delete(conn, new List<Param>());
        }

        /// <summary>
        /// Delete specified rows (the primary key is used to match the rows to delete)
        /// </summary>
        /// <param name="rows">The list of rows to delete</param>
        /// <returns>The number of affected rows.</returns>
        public static int Delete(this DbConnection conn, IEnumerable<Row> rows)
        {
            int i = 0;
            foreach (Row row in rows)
                i += Delete(conn, row);
            return i;
        }

        /// <summary>
        /// Delete the specified row (the primary key is used to match the row to delete)
        /// </summary>
        /// <param name="row">The row to delete.</param>
        /// <returns>The number of affected rows.</returns>
        public static int Delete(this DbConnection conn, Row row)
        {
            List<Param> @params = new List<Param>();
            @params.Add(new Param(Field.@Id, DbOperator.EQUAL, row._Id));

            return Delete(conn, @params);
        }

        /// <summary>
        /// Deletes the row in the database item using the specified primary key
        /// as the search criteria
        /// </summary>
        /// <param name="PK_Id">Value of the primary key</param>
        /// <returns>1 if a row was deleted, 0 otherwise</returns>
        public static int Delete(this DbConnection conn, System.Int64 PK_Id)
        {
            List<Param>  @params = new List<Param>();

            @params.Add(new Param(Field.@Id, DbOperator.EQUAL, PK_Id));

            return Delete(conn, @params);
        }


        /// <summary>
        /// Deletes the rows in the database item using the specified Param as the search criteria.
        /// </summary>
        /// <param name="paramWhere">The search criteria.</param>
        /// <returns>The number of deleted rows.</returns>
        public static int Delete(this DbConnection conn, Param paramWhere)
        {
            List<Param> @params = new List<Param>();
            @params.Add(paramWhere);
            return Delete(conn, @params);
        }

        /// <summary>
        /// Deletes the rows in the database item using the specified
        /// collection of Param as the search criteria
        /// </summary>
        /// <param name="paramsWhere">The set of search criteria.</param>
        /// <returns>The number of deleted rows.</returns>
        public static int Delete(this DbConnection conn, IEnumerable<Param> paramsWhere)
        {
            using (var command = new SQLiteCommand()) {
                command.CommandText = "DELETE FROM \"DatabaseModel\" ";
                BuildWhereClause(command, paramsWhere);
                return conn.ExecuteNonQuery(command);
            }
        }

        #endregion

        #region private functions

        class WhereItem
        {
            public List<SQLiteParameter> Parameters = new List<SQLiteParameter>();
            public string Sql = string.Empty;
        }
        static readonly string[] DbOperators = { " = ", " <> ", " >= ", " > ", " <= ", " < ", " LIKE ", " NOT LIKE ", " IS NULL ", " IS NOT NULL ", " IN ", " NOT IN " };
        static void BuildWhereClause(SQLiteCommand command, IEnumerable<Param> paramsWhere)
        {
            if (paramsWhere == null || paramsWhere.Count() <= 0)
                return;
            List<WhereItem> where_items = new List<WhereItem>();
            int i = 0;
            const string param_prefix = "@W";
            foreach (var grouped_params in from p in paramsWhere
                                           where p.DbOperator == DbOperator.IN || p.DbOperator == DbOperator.NOT_IN
                                           group p by new { p.ColumnNameEscaped, p.DbOperator }) {
                WhereItem where_item = new WhereItem();
                foreach (var param in grouped_params) {
                    SQLiteParameter parameter = new SQLiteParameter(param_prefix + i++, param.DbType);
                    parameter.Value = param.Value;
                    where_item.Parameters.Add(parameter);
                }
                where_item.Sql = grouped_params.Key.ColumnNameEscaped + DbOperators[(int)grouped_params.Key.DbOperator]
                    + "(" + string.Join(", ", from p in where_item.Parameters select p.ParameterName) + ")";
                where_items.Add(where_item);
            }
            foreach (var param in from p in paramsWhere
                                  where p.DbOperator != DbOperator.IN && p.DbOperator != DbOperator.NOT_IN
                                  select p) {
                WhereItem where_item = new WhereItem();
                SQLiteParameter parameter = new SQLiteParameter(param_prefix + i++, param.DbType);
                parameter.Value = param.Value;
                where_item.Parameters.Add(parameter);
                where_item.Sql = param.ColumnNameEscaped + DbOperators[(int)param.DbOperator] + parameter.ParameterName;
                where_items.Add(where_item);
            }
            command.CommandText += " WHERE " + string.Join(" AND ", from w in where_items select w.Sql);
            where_items.ForEach(w => command.Parameters.AddRange(w.Parameters.ToArray()));
        }
        static void BuildOrderByClause(SQLiteCommand command, IEnumerable<Param> @params)
        {
            if (@params == null || @params.Count() <= 0)
                return;
            command.CommandText += " ORDER BY "
                + string.Join(", ", from p in @params select p.ColumnNameEscaped + (p.OrderByAsc ? " ASC" : " DESC"));
        }


        #endregion
    }
}
